package devicedbgui.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Hashtable;

/** Interface to database for acquiring Exe information */
public class ExeDAO {
     /** Stores Connection status */
    private Connection conn;
    /** Constructor used to pass connection status to object
     * @param conn  connection status
     */
    public ExeDAO(Connection conn)
    {
        this.conn = conn;
    }
    /** Selects all exes in database
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Exe> selectAll() throws Exception {
        ArrayList<Exe> result = new ArrayList<Exe>();

        try {
            String query = "SELECT * FROM exe;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Exe pObj = new Exe(
                        rs.getInt("idexe"),
                        rs.getString("description"),                        
                        rs.getInt("Build_idBuild")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
     /** Inserts a new exe into database
     * 
     * @param pObj
     * @throws java.lang.Exception
     */
    public void insert(Exe pObj) throws Exception {
       
        try {
            String insert = "INSERT INTO exe(" +
                    "description," +                    
                    "Build_idBuild)" +
                    "VALUES('" + pObj.description + "','" +                                 
                                 pObj.Build_idBuild + "');";

            
            PreparedStatement pstmt = conn.prepareStatement(insert);
            pstmt.executeUpdate();           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
    /** Updates database with the modified exe information 
 * 
 * @param pObj
 * @throws java.lang.Exception
 */
    public void update(Exe pObj) throws Exception {        
        try {

            String update = "UPDATE exe SET " +
                    "description = '" + pObj.description + "'," +                    
                    "Build_idBuild = '" + pObj.Build_idBuild +
                    "' WHERE idexe = '" + pObj.idexe + "';";
            
           
            PreparedStatement pstmt = conn.prepareStatement(update);
            pstmt.executeUpdate();
            pstmt.close();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Deletes a value from database
 * @param pId
 * @throws java.lang.Exception
 */
    public void delete(int pId) throws Exception {
       
        try {
            String delete = "DELETE FROM exe WHERE idexe = '" + pId + "';";
          
            PreparedStatement pstmt = conn.prepareStatement(delete);
            pstmt.executeUpdate();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
    
    /** Chooses exe based on build id
     * 
     * @param idBuild 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Exe> selectByBuild(int idBuild) throws Exception {
        ArrayList<Exe> result = new ArrayList<Exe>();
        try {
            String query = "SELECT * FROM exe where Build_idBuild = " + idBuild +";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Exe pObj = new Exe(
                        rs.getInt("idexe"),
                        rs.getString("description"),                        
                        rs.getInt("Build_idBuild")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    
    /** Gets last insert id
     * @return ret
     * @throws Exception
     */
    public int getLastID() throws Exception {
        int ret = -1;
        try {
            String getLastID = "select LAST_INSERT_ID();";
            
            PreparedStatement pstmt = conn.prepareStatement(getLastID);
            ResultSet rs = pstmt.executeQuery();           
            if (rs.next()) {
                ret = rs.getInt(1);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return ret;
    }
    
    /** Gets Exe Statistics
     * @return ret
     * @throws Exception 
     */
    public ArrayList<ExeStats> getExeStats() throws Exception
    {
        ArrayList<ExeStats> ret = new ArrayList<ExeStats>();
        try {
            // get failed
            String query = "select e.description, count(*) as numOfFailed from TestResult tr, TestCase tc, " +
                    "exe e where e.idexe = tc.exe_idexe " +
                    "and tc.idTestCase = tr.TestCase_idTestCase and tr.description = 'Test Failed' group by e.description;";
            
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();           
            while (rs.next()) {
                ExeStats pObj = new ExeStats();
                pObj.exeDescription = rs.getString("description");
                pObj.numberOfFailed = rs.getInt("numOfFailed");
                ret.add(pObj);
            }

            // continue to get passed
            ArrayList<ExeStats> passed = new ArrayList<ExeStats>();
            query = "select e.description, count(*) as numOfPassed from TestResult tr, TestCase tc, " +
                    "exe e where e.idexe = tc.exe_idexe " +
                    "and tc.idTestCase = tr.TestCase_idTestCase and tr.description = 'Test Passed' group by e.description;";
            pstmt = conn.prepareStatement(query);
            rs = pstmt.executeQuery();           
            while (rs.next()) {
                ExeStats pObj = new ExeStats();
                pObj.exeDescription = rs.getString("description");
                pObj.numberOfPassed = rs.getInt("numOfPassed");
                passed.add(pObj);
            }
            
            // merge two arrays
            for (ExeStats item : ret) {
                for (ExeStats pass : passed) {
                    if (item.exeDescription.equals(pass.exeDescription)) {
                        item.numberOfPassed = pass.numberOfPassed;
                    }
                }
            }
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return ret;
    }
    
    /** Gets all Test Case Stats associated with a given exe description
     * @param exeDescription
     * @return ret
     * @throws Exception 
     */
    public ArrayList<TestCaseStats> getAllTestCaseDescriptions(String exeDescription) throws Exception
    {
        ArrayList<TestCaseStats> ret = new ArrayList<TestCaseStats>();
        
        try {
            ArrayList<String> testCaseDescs = new ArrayList<String>(); // all test case descriptions associated with the given exe
                        
            String query = "select tc.description from TestResult tr, TestCase tc, " +
                    "exe e where e.description = '" + exeDescription + "' and e.idexe = tc.exe_idexe " +
                    "and tc.idTestCase = tr.TestCase_idTestCase;";
            
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();           
            while (rs.next()) {
                testCaseDescs.add(rs.getString("description"));
            }
            
            // parse test case descs to build the result
            Hashtable<String, TestCaseStats> ht = new Hashtable<String, TestCaseStats>();
            String[] tmp;
            String item, result;
            int pos;
            TestCaseStats tcs;
            for (String tcDesc : testCaseDescs){
                tmp = tcDesc.split(",");
                for (int i = 0; i < tmp.length; i++){
                    item = tmp[i].trim();
                    if (item.length() == 0) {
                        // ignore empty string
                        continue;
                    }
                    pos = item.indexOf("failed");
                    if (pos == -1) {
                        pos = item.indexOf("successful");
                    }
                    if (pos == -1) {
                        pos = item.indexOf("succesful");
                    }
                    
                    // get test case and result
                    tcs = new TestCaseStats();
                    tcs.testCase = item.substring(0, pos - 1).trim();
                    result = item.substring(pos).trim(); // failed, successful, succesful
                    if (ht.containsKey(tcs.testCase)){ // contained, just update
                        if (result.equals("failed")) {
                            ht.get(tcs.testCase).numberOfFailed++;
                        }
                        else if (result.equals("successful") || result.equals("succesful")) {
                            ht.get(tcs.testCase).numberOfPassed++;
                        }
                    }
                    else { // add new
                        if (result.equals("failed")) {
                            tcs.numberOfFailed++;
                        }
                        else if (result.equals("successful") || result.equals("succesful")) {
                            tcs.numberOfPassed++;
                        }
                        ht.put(tcs.testCase, tcs);
                    }
                }
            }
            
            // get return result
            ret.addAll(ht.values());
            
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return ret;
    }
}
